﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
///Exam 的摘要说明
/// </summary>
public class Exam
{
    public Exam()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    #region 考试类型管理
    public static DataTable ExamType_GetAllList()
    {
        string cmdText = "select * from ExamType ";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }
    public static DataTable ExamType_GetList(string emId)
    {
        string cmdText = "select * from ExamType where EmID=@EmId";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@EmId",SqlDbType.NVarChar,10)
        };
        parms[0].Value = emId;
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    public static string ExamType_GetExamType(string timeID)
    {
        string cmdText = @"Select EmType from ExamTime as a,ExamType as b where a.EmID = b.EmID and a.TimeID = @TimeID ";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@TimeID",SqlDbType.Int)
        };
        parms[0].Value = timeID;
        object o = Dbase.ExecuteScalar(CommandType.Text, cmdText, parms);
        if (o != null)
        {
            return o.ToString();
        }
        else
            throw new Exception("没有该类型，结果出错，请联系管理员");
    }
    #endregion

    #region 考试等级管理
    /// <summary>
    /// 通过考试编号获取考试等级名称
    /// </summary>
    /// <param name="EmID">考试编号，例如四级考试就有例如201504编号</param>
    /// <returns>返回datatable对象</returns>
    public static DataTable getRankNameByEmID(string EmID)
    {
        string cmdText = "Select EmRankName from ExamRank where EmID=@EmID order by RankIndex";
        SqlParameter[] parms = new SqlParameter[] 
        { 
            new SqlParameter("@EmID", SqlDbType.VarChar, 10) 
        };
        parms[0].Value = EmID.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    public static DataTable ExamRank_GetRankName(string timeID)
    {
        string cmdText = "Select EmRankName from ExamRank as a,ExamTime as b where a.EmID = b.EmID and TimeID=@TimeID order by RankIndex";
        SqlParameter[] parms = new SqlParameter[] 
        { 
            new SqlParameter("@TimeID", SqlDbType.VarChar, 10) 
        };
        parms[0].Value = timeID.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }

    #endregion

    #region 考试时间管理
    public static bool insertExamTime(string emid, string year, string num, string btime, string etime, string ename, string examgrade)
    {
        try
        {
            string cmdText = @"insert into ExamTime(EmID,EmYear,EmNum,ApplyBeginTime,ApplyEndTime,EmAllName,EmLimitGrade)
                                values ('" + emid + "','" + year + "','" + num + "','" + btime + "','" + etime + "','" + ename + "','" + examgrade + "')";
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, null);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    /// <summary>
    /// 得到当前时间正在报名的考试（提前一个月）
    /// </summary>
    /// <returns>返回DataTable对象</returns>
    public static DataTable ExamTime_GetCurrExamList()
    {
        string cmdText = @"Select * from ExamTime
                            Where GETDATE()>= DATEADD(day,-30,ApplyBeginTime) and GETDATE()< DATEADD(day,1,ApplyEndTime) ";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }
    /// <summary>
    /// 得到最近的8次考试
    /// </summary>
    /// <returns></returns>
    public static DataTable ExamTime_GetTopList()
    {
        string cmdText = @"Select top 8 * from ExamTime order by ApplyEndTime desc";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }
    /// <summary>
    /// 通过考试编号得到考试时间信息
    /// </summary>
    /// <param name="emId"></param>
    /// <returns></returns>
    public static DataTable getEmTimeByEmId(string timeID)
    {
        string cmdText = "select * from ExamTime where TimeID = @TimeID";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@TimeID",SqlDbType.Int)
        };
        parms[0].Value = timeID;
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 通过考试编号得到考试时间信息
    /// </summary>
    /// <param name="emId"></param>
    /// <returns></returns>
    public static DataTable getEmTimeByEmId(string emID, string emYear, string emNum)
    {
        string cmdText = "select * from ExamTime where EmID = @EmID and EmYear = @EmYear and EmNum = @EmNum";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@EmID",SqlDbType.NVarChar,10),
            new SqlParameter("@EmYear",SqlDbType.Char,4),
            new SqlParameter("@EmNum",SqlDbType.TinyInt),
        };
        parms[0].Value = emID;
        parms[1].Value = emYear;
        parms[2].Value = emNum;
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 判断本考试是否过期
    /// </summary>
    /// <param name="timeID"></param>
    /// <returns></returns>
    public static bool ExamTime_IsTimeOut(string timeID)
    {
        DataTable dt = Exam.getEmTimeByEmId(timeID);
        if (dt.Rows.Count > 0)
        {
            DataRow dr = dt.Rows[0];
            DateTime startTime = DateTime.Parse(dr["ApplyBeginTime"].ToString());
            DateTime endTime = DateTime.Parse(dr["ApplyEndTime"].ToString());
            if (DateTime.Now < startTime || DateTime.Now.AddDays(-1) > endTime)
            {
                return false;
            }
            else
                return true;
        }
        return false;
    }
    #endregion

    #region 报名信息管理
    public static DataTable ApplyInfoRank_GetList(string timeID)
    {
        string cmdText = @"select distinct EmRankName
                             from ApplyInfo,ExamTime
                            Where ApplyInfo.EmID = ExamTime.EmID and ApplyInfo.EmYear = ExamTime.EmYear and ApplyInfo.EmNum = ExamTime.EmNum and ExamTime.TimeID = @TimeID and ApplyInfo.ApplyState = 2
                                    order by ApplyFhTime desc";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@TimeID",SqlDbType.Int)
        };
        parms[0].Value = timeID.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }

    /// <summary>
    /// 得到所有报名信息列表
    /// </summary>
    /// <returns></returns>
    public static DataTable ApplyInfo_GetList(string timeID)
    {
        string cmdText = @"select ApplyInfo.* 
                             from ApplyInfo,ExamTime
                            Where ApplyInfo.EmID = ExamTime.EmID and ApplyInfo.EmYear = ExamTime.EmYear and ApplyInfo.EmNum = ExamTime.EmNum and ExamTime.TimeID = @TimeID
                                    order by ApplyFhTime desc";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@TimeID",SqlDbType.Int)
        };
        parms[0].Value = timeID.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 根据报名号获得报名信息
    /// </summary>
    /// <param name="applyID"></param>
    /// <returns></returns>
    public static DataTable ApplyInfo_GetInfoByID(string applyID)
    {
        string cmdText = "select * from ApplyInfo where ApplyID=@ApplyID";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@ApplyID",SqlDbType.Int)
        };
        parms[0].Value = applyID.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }

    /// <summary>
    /// 通过考试编号、年份、次数、身份证号得到报名信息
    /// </summary>
    /// <param name="studIdCard">身份证号码</param>
    /// <param name="emId">考试编号</param>
    /// <param name="emYear">考试年份</param>
    /// <param name="emNum">考试次数</param>
    /// <returns>返回一个datatable对象</returns>
    public static DataTable ApplyInfo_GetInfo(string studIdCard, string timeID)
    {
        string cmdText = @"Select * from ApplyInfo as a,ExamTime as b
                            where StudIdcard=@StudIdcard and a.EmID=b.EmID and a.EmYear=b.EmYear and a.EmNum=b.EmNum and b.TimeID = @TimeID";
        SqlParameter[] parms = new SqlParameter[]{													   
				new SqlParameter("@StudIdcard",SqlDbType.VarChar,18),
				new SqlParameter("@TimeID",SqlDbType.Int),
			};
        parms[0].Value = studIdCard.Trim();
        parms[1].Value = timeID.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 插入报名信息
    /// </summary>
    /// <param name="studIdcard">身份证号</param>
    /// <param name="emId">考试编号</param>
    /// <param name="emRankName">考试等级名称</param>
    /// <returns>返回datatable对象</returns>
    public static bool ApplyInfo_Insert(string studIdcard, string timeID, string emRankName)
    {
        try
        {
            string cmdText = @"insert into ApplyInfo 
                                        (   StudIdcard,StudName,StudID,StudTel,StudSex,StudDeptName,StudClassName,
                                            StudFrom,StudPic,EmID,EmRankName,EmAllName,
                                            EmYear,EmNum,EmCost,ApplyState,ApplyCtTime
                                        )
                               Select distinct StudIdcard,StudName,StudID,StudTel,StudSex,StudDeptName,StudClassName,StudFrom,StudPic,ExamRank.EmID,
                                    EmRankName,EmAllName,EmYear,EmNum,EmCost,0,GETDATE()
                                from StudentInfo,ExamRank,ExamTime 
                               where ExamRank.EmID = ExamTime.EmID 
                                    and ExamTime.TimeID = @TimeID and
                                    StudIdcard=@StudIdcard and EmRankName=@EmRankName ";
            SqlParameter[] parms = new SqlParameter[]{			
			            new SqlParameter("@StudIdcard",SqlDbType.VarChar,18),								   
					    new SqlParameter("@TimeID",SqlDbType.Int),	
                        new SqlParameter("@EmRankName",SqlDbType.NVarChar,20)
			};
            parms[0].Value = studIdcard.Trim();
            parms[1].Value = timeID.Trim();
            parms[2].Value = emRankName.Trim();
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception ex)
        {
            return false;

        }
    }
    /// <summary>
    /// 更新报名信息
    /// </summary>
    /// <param name="studIdcard">身份证号</param>
    /// <param name="emId">考试编号</param>
    /// <param name="emYear">考试年份</param>
    /// <param name="emNum">考试次数</param>
    /// <returns>返回一个boolean</returns>
    public static bool BMInfo_Update(string studIdcard, string timeID, string emRankName)
    {
        try
        {
            string cmdText = @"update ApplyInfo set EmRankName=@EmRankName
                                From ExamTime as a,ApplyInfo as b
                                where StudIdcard=@StudIdcard and a.EmID=b.EmID and a.EmYear=b.EmYear and a.EmNum=b.EmNum";
            SqlParameter[] parms = new SqlParameter[]{			
			        new SqlParameter("@StudIdcard",SqlDbType.VarChar,18),
                    new SqlParameter("@TimeID",SqlDbType.Int),
                    new SqlParameter("@EmRankName",SqlDbType.NVarChar,20)
			};
            parms[0].Value = studIdcard.Trim();
            parms[1].Value = timeID.Trim();
            parms[2].Value = emRankName.Trim();
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());

        }
    }

    /// <summary>
    /// 保存报名信息
    /// </summary>
    /// <param name="studIdcard">身份证号</param>
    /// <param name="emYear">考试年份</param>
    /// <param name="emNum">考试次数</param>
    /// <param name="emId">考试编号</param>
    /// <param name="emRankName">考试等级名称</param>
    /// <returns>返回一个保存的boolean</returns>
    public static bool BMInfo_Save(string studIdcard, string timeID, string emRankName)
    {
        try
        {
            int count = ApplyInfo_GetInfo(studIdcard, timeID).Rows.Count;
            if (count == 0)
            {
                return ApplyInfo_Insert(studIdcard, timeID, emRankName);
            }
            else if (count == 1)
            {
                return BMInfo_Update(studIdcard, timeID, emRankName);
            }
            else
                throw new Exception("系统数据故障，请联系管理员");
        }
        catch
        {
            return false;
        }

    }
    /// <summary>
    /// 重置报名数据
    /// </summary>
    /// <param name="studIdcard">学生身份证号</param>
    /// <param name="emId">考试id</param>
    /// <param name="emYear">考试年份</param>
    /// <param name="emNum">考试次数</param>
    public static bool ApplyInfo_Reset(string studIdcard, string timeID)
    {
        try
        {
            SqlParameter[] parms = new SqlParameter[]{			
			    new SqlParameter("@StudIdcard",SqlDbType.VarChar,18),
			    new SqlParameter("@TimeID",SqlDbType.Int),
			};
            parms[0].Value = studIdcard.Trim();
            parms[1].Value = timeID.Trim();
            Dbase.ExecuteNonQuery(CommandType.StoredProcedure, "BM_Restart", parms);
            return true;
        }
        catch (Exception ex)
        {
            return false;

        }
    }
    /// <summary>
    /// 通过订单号获得报名信息
    /// </summary>
    /// <param name="orderID"></param>
    /// <returns></returns>
    public static DataTable ApplyInfo_GetInfoByOrder(string orderID)
    {
        string cmdText = "Select * from ApplyInfo Where OrderID = @OrderID ";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@OrderID",SqlDbType.Int)
        };
        parms[0].Value = orderID;
        DataTable dt = (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
        if (dt.Rows.Count == 1)
            return dt;
        else
            throw new Exception("系统故障，请联系管理员");
    }
    /// <summary>
    /// 通过身份证号得到所有报名的信息
    /// </summary>
    /// <param name="IdCard">身份证号</param>
    /// <returns>含有同一个人的所有报名信息</returns>
    public static DataTable ApplyInfo_GetHistoryListByIdCard(string IdCard)
    {
        string cmdText = "select * from ApplyInfo where StudIdcard=@StudIdCard";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@StudIdCard", SqlDbType.VarChar, 18)
        };
        parms[0].Value = IdCard.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    public static bool updateApplyInfoByApplyId(string applyId, string deptName, string className, string rankName)
    {
        try
        {
            string cmdText = "update ApplyInfo set StudDeptName=@deptName,StudClassName=@className,EmRankName=@rankName where ApplyID=@applyId";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@deptName",SqlDbType.NVarChar,20),
                new SqlParameter("@className",SqlDbType.VarChar,20),
                new SqlParameter("@rankName",SqlDbType.NVarChar,20),
                new SqlParameter("@applyId",SqlDbType.Int)
            };
            parms[0].Value = deptName.Trim();
            parms[1].Value = className.Trim();
            parms[2].Value = rankName.Trim();
            parms[3].Value = applyId.Trim();
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }


    #region 专升本报名管理
    public static void ZhuanShengBenBMInfo_Save(string studIdcard, string timeID, string emRankName, string[] zhuanShengBenInfo)
    {
        int count = ApplyInfo_GetInfo(studIdcard, timeID).Rows.Count;
        if (count == 0)
        {
            ApplyInfo_Insert(studIdcard, timeID, emRankName);
            DTable.ExeInsert("ZhuanShengBenInfo", zhuanShengBenInfo);
        }
        else if (count == 1)
        {
            BMInfo_Update(studIdcard, timeID, emRankName);
            string[] keyValues = new string[] { 
                timeID,
                studIdcard,
            };
            DTable.ExeUpdate("ZhuanShengBenInfo", zhuanShengBenInfo, keyValues);
        }
        else
            throw new Exception("系统数据故障，请联系管理员");

    }
    #endregion
    #endregion

    #region 订单管理
    /// <summary>
    /// 创建订单
    /// </summary>
    /// <param name="studIdcard">学生身份证号</param>
    /// <param name="emRankName">考试等级</param>
    /// <param name="emYear">考试年份</param>
    /// <param name="emNum">考试次数</param>
    /// <param name="emId">考试编号</param>
    /// <returns></returns>
    public static int CreateOrderInfo(string applyID)
    {
        try
        {
            //string cmdText = "BM_CreateOrderInfo(@SFZH,@TypeID,@ExamYear,@ExamNumber)";
            SqlParameter[] parms = new SqlParameter[]{			
			    new SqlParameter("@ApplyID",SqlDbType.Int),
                new SqlParameter("@OrderID",SqlDbType.Int)
			};
            parms[0].Value = applyID.Trim();
            parms[1].Direction = ParameterDirection.Output;
            Dbase.ExecuteNonQuery(CommandType.StoredProcedure, "BM_CreateOrder", parms);

            return (int)parms[1].Value;
        }
        catch (Exception ex)
        {
            return 0;

        }
    }

    /// <summary>
    /// 通过订单号获取订单信息
    /// </summary>
    /// <param name="orderid">订单号</param>
    /// <returns>返回一个包含定的那信息datatable对象</returns>
    public static DataTable getOrderInfoByOrderID(string orderid)
    {
        //string cmdText = "Select * from Exam_OrderInfo where OrderID=@OrderID";
        string cmdText = "select * from OrderInfo where OrderID=@OrderID";
        SqlParameter[] parms = new SqlParameter[]{													   
			 new SqlParameter("@OrderID",SqlDbType.Int),
		};
        parms[0].Value = orderid.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 更新订单信息
    /// </summary>
    /// <param name="orderId">订单号</param>
    /// <param name="state">状态</param>
    public static bool Update_OrderState(string orderId, string state)
    {
        try
        {
            string cmdText = "update OrderInfo set OrderState=@OrderState where OrderID=@OrderID";
            SqlParameter[] parms = new SqlParameter[]{			
                new SqlParameter("@OrderID",SqlDbType.Int),
                new SqlParameter("@OrderState",SqlDbType.TinyInt)
            };
            parms[0].Value = orderId.Trim();
            parms[1].Value = state.Trim();
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception ex)
        {
            return false;

        }
    }
    /// <summary>
    /// 通过订单号获取订单状态
    /// </summary>
    /// <param name="orderId">订单号</param>
    /// <returns>返回订单状态</returns>
    public static int get_OrderState(string orderId)
    {
        try
        {
            //string cmdText = "select OrderState from Exam_OrderInfo where OrderID=@OrderID";
            string cmdText = "select OrderState from OrderInfo where OrderID=@OrderID";
            SqlParameter[] parms = new SqlParameter[]{			
		        new SqlParameter("@OrderID",SqlDbType.Int),
			};
            parms[0].Value = orderId.Trim();
            object o = Dbase.ExecuteScalar(CommandType.Text, cmdText, parms);
            int i = -1;
            if (int.TryParse(o.ToString(), out i))
            {
                return i;
            }
            else
            {
                return -1;
            }

        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
    }
    /// <summary>
    /// 得到单个用户所有的订单信息
    /// </summary>
    /// <param name="idCard"></param>
    /// <returns></returns>
    public static DataTable getOrderInfosByIdCard(string idCard)
    {
        string cmdText = @"select ApplyID,EmYear,EmNum,OrderInfo.OrderID,OrderInfo.EmAllName,OrderInfo.EmCost,OrderCtTime,OrderFhTime,OrderState,EmRankName
                            from  OrderInfo,ApplyInfo 
                            where OrderInfo.StudIdcard=@idCard
		                        and ApplyInfo.StudIdcard=@idCard and ApplyInfo.OrderID=OrderInfo.OrderID";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@idCard",SqlDbType.VarChar,18)
        };
        parms[0].Value = idCard.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 通过身份证号和考试编号得到订单信息
    /// </summary>
    /// <param name="studIdCard">身份证号</param>
    /// <param name="emId">考试编号</param>
    /// <returns></returns>
    public static DataTable getOrderInfoByMsg(string studIdCard, string emId)
    {
        string cmdText = "select * from OrderInfo where StudIdcard=@StudIdcard and EmID=@EmId";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@StudIdcard",SqlDbType.VarChar,18),
            new SqlParameter("@EmId",SqlDbType.VarChar,10)
        };
        parms[0].Value = studIdCard.Trim();
        parms[1].Value = emId.Trim();

        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    public static bool Order_Complete(string orderId)
    {
        try
        {
            string cmdText = @" Update OrderInfo set OrderState= 9 , OrderFhTime=GETDATE() where OrderID=@OrderID 
                                Update ApplyInfo set ApplyState= 2 , ApplyFhTime=GETDATE() where OrderID=@OrderID ";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@OrderID",SqlDbType.Int)
            };
            parms[0].Value = orderId.Trim();
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch
        {
            return false;
        }
    }
    #endregion

    #region 历史成绩管理
    /// <summary>
    /// 通过报名号获取历史成绩
    /// </summary>
    /// <param name="applyId"></param>
    /// <returns></returns>
    public static DataTable getHistoryScoreByApplyId(string applyId)
    {
        //string cmdText = "Select * from Exam_HistoryScore where SFZH=@SFZH";
        string cmdText = "select * from HistoryScore where ApplyID=@ApplyID";
        SqlParameter[] parms = new SqlParameter[]{	
			new SqlParameter("@ApplyID",SqlDbType.VarChar,20)
		};
        parms[0].Value = applyId.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 得到同一个用户的历史成绩信息
    /// </summary>
    /// <param name="idCard"></param>
    /// <returns></returns>
    public static DataTable getHistoryScoreByIdCard(string idCard)
    {
        string cmdText = @"select * from HistoryScore,ApplyInfo where HistoryScore.ApplyID=ApplyInfo.ApplyID and StudIdcard=@idCard order by ScoreGetTime asc";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@idCard",SqlDbType.VarChar,18)
        };
        parms[0].Value = idCard.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    #endregion

    #region 考试允许范围管理
    /// <summary>
    /// 判断这名用户是否可以报名本考试
    /// </summary>
    /// <param name="timeID"></param>
    /// <param name="userinfo">身份证号或用户名比可</param>
    /// <returns></returns>
    public static bool ExamTimeIsPermit(string timeID, string userinfo)
    {
        string cmdText = @" Select * from ApplyUser 
                            Where TimeID = @TimeID and ( UserID = @UserInfo Or StudIDCard = @UserInfo) ";
        SqlParameter[] parms = new SqlParameter[]{													   
			 new SqlParameter("@TimeID",SqlDbType.Int),
             new SqlParameter("@UserInfo",SqlDbType.NVarChar,20),
		};
        parms[0].Value = timeID.Trim();
        parms[1].Value = userinfo.Trim();
        DataTable dt = (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
        return dt.Rows.Count > 0;
    }
    /// <summary>
    /// 判断这名用户的身份是否允许参加本考试（校内学生与校外学生的身份）
    /// </summary>
    /// <param name="timeID"></param>
    /// <param name="schoolFlag"></param>
    /// <returns></returns>
    public static bool ExamTimeIsPermit(string timeID, bool schoolFlag)
    {
        string cmdText = @"Select * From ExamTime as a,ExamType as b
                           Where a.EmID = b.EmID and TimeID = @TimeID and b.EmLimitFlag = @SchoolFlag ";
        SqlParameter[] parms = new SqlParameter[]{													   
			 new SqlParameter("@TimeID",SqlDbType.Int),
             new SqlParameter("@SchoolFlag",SqlDbType.Bit),
		};
        parms[0].Value = timeID.Trim();
        parms[1].Value = schoolFlag.ToString().Trim();
        DataTable dt = (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
        return dt.Rows.Count > 0;
    }
    #endregion


    /// <summary>
    /// 得到报名成功的报名信息
    /// </summary>
    /// <returns></returns>
    public static DataTable getSucceedApplyInfos()
    {
        string cmdText = "select * from ApplyInfo where ApplyState = '2'";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }
    /// <summary>
    /// 得到所有订单信息
    /// </summary>
    /// <returns></returns>
    public static DataTable getAllOrderInfos()
    {
        string cmdText = @"select OrderInfo.StudIdcard,StudID,ApplyID,OrderInfo.OrderID,OrderInfo.EmAllName,OrderInfo.EmCost,OrderCtTime,OrderFhTime,OrderState,EmRankName,ApplyInfo.EmID,EmYear,EmNum from               OrderInfo,ApplyInfo where  ApplyInfo.OrderID=OrderInfo.OrderID order by OrderFhTime desc";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }
    /// <summary>
    /// 得到订单有效期
    /// </summary>
    /// <param name="days"></param>
    /// <returns></returns>
    public static DataTable GetOrderListByDays(int days)
    {
        string cmdText = @" select b.* from OrderInfo as a,ApplyInfo as b where a.OrderID = b.OrderID and OrderState>=5 and OrderState <= 6 and DATEDIFF(DAY,OrderCtTime,GETDATE()) < @Days ";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@Days",SqlDbType.Int)
        };
        parms[0].Value = days.ToString();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);

    }




    /// <summary>
    /// 得到考试名字和状态限定的报名记录
    /// </summary>
    /// <param name="examAllName"></param>
    /// <param name="applyState"></param>
    /// <returns></returns>
    public static DataTable getSearchApplyInfos(string examAllName, string applyState)
    {
        string cmdText = "select * from ApplyInfo where EmAllName=@examAllName and ApplyState=@applyState";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@examAllName",SqlDbType.NVarChar,20),
            new SqlParameter("@applyState",SqlDbType.Int)
        };
        parms[0].Value = examAllName.Trim();
        parms[1].Value = applyState.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }


    #region 统计功能
    /// <summary>
    /// 导出对帐单
    /// </summary>
    /// <param name="startTime"></param>
    /// <param name="endTime"></param>
    /// <param name="emId"></param>
    /// <returns></returns>
    public static DataTable OrderInfo_GetReconcileList(string startTime, string endTime)
    {
        if (startTime != "" && endTime != "")
        {
            string cmdText = @"select StudID,StudName,StudClassName,Sum(Isnull(EmCost,0)) 
                                 from OrderInfo,ApplyInfo
                                where OrderInfo.OrderID = ApplyInfo.OrderID 
                                        And OrderFhTime>@startTime and OrderFhTime<=@endTime and OrderState=9
                                Group by ApplyInfo.StudID,ApplyInfo.StudName,ApplyInfo.StudClassName";

            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@startTime",SqlDbType.DateTime),
                new SqlParameter("@endTime",SqlDbType.DateTime)
            };
            parms[0].Value = startTime;
            parms[1].Value = endTime;
            return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
        }
        else
            throw new Exception("时间不能为空");

    }
    #endregion


    #region 申请及修改管理
    /// <summary>
    /// 得到申请修改的用户信息
    /// </summary>
    /// <returns></returns>
    public static DataTable getWillRecord(string timeID)
    {
        int id = 0;
        if (int.TryParse(timeID, out id))
        {

        }
        string cmdText = @"select a.ApplyID, AskReason,OldRankName,NewRankName,StudName,StudID
                            from AskRecordInfo as a,ApplyInfo as b,ExamTime as c 
                            where a.ApplyID = b.ApplyID and AskState=1 and b.EmID = c.EmID and b.EmYear = c.EmYear and b.EmNum = c.EmNum and c.TimeID = @TimeID";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@TimeID",SqlDbType.Int),
        };
        parms[0].Value = id.ToString();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 更新申请记录更新后的报名级别
    /// </summary>
    /// <param name="applyId"></param>
    /// <param name="newRankName"></param>
    /// <returns></returns>
    public static bool updateRecord(string applyId, string newRankName, string reason)
    {
        try
        {
            string cmdText = "update AskRecordInfo set NewRankName=@newRankName,AskReason=@reason where ApplyID=@applyId";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@applyId",SqlDbType.Int),
                new SqlParameter("@newRankName",SqlDbType.NVarChar,20),
                new SqlParameter("@reason",SqlDbType.NVarChar,100)
            };
            parms[0].Value = applyId;
            parms[1].Value = newRankName;
            parms[2].Value = reason;
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }

    }
    public static bool updateRecordState(string applyId, string askState)
    {
        try
        {
            string cmdText = "update AskRecordInfo set AskState=@askState where ApplyID=@applyId";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@applyId",SqlDbType.Int),
                new SqlParameter("@askState",SqlDbType.TinyInt)
            };
            parms[0].Value = applyId;
            parms[1].Value = askState;
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    /// <summary>
    /// 插入申请记录信息
    /// </summary>
    /// <param name="applyId"></param>
    /// <returns></returns>
    public static bool insertRecord(string applyId, string newRankName, string reason)
    {
        try
        {
            string cmdText = @"insert into AskRecordInfo(ApplyID,AskState,AskReason,OldRankName,NewRankName)
                                select @applyId,1,@AskReason,EmRankName,@NewRankName from ApplyInfo where ApplyID=@applyId";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@applyId",SqlDbType.Int),
                new SqlParameter("@NewRankName",SqlDbType.NVarChar,20),
                new SqlParameter("@AskReason",SqlDbType.NVarChar,2000),
            };
            parms[0].Value = applyId;
            parms[1].Value = newRankName;
            parms[2].Value = reason;
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    public static DataTable getRecordByApplyId(string applyId)
    {
        string cmdText = "select * from AskRecordInfo where ApplyID=@applyId";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@applyId",SqlDbType.Int)
        };
        parms[0].Value = applyId;
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    public static bool updateApplyRank(string applyId, string rankName)
    {
        try
        {
            string cmdText = "update ApplyInfo set EmRankName=@rankName where ApplyID=@applyId";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@applyId",SqlDbType.Int),
                new SqlParameter("@rankName",SqlDbType.NVarChar,20)
            };
            parms[0].Value = applyId;
            parms[1].Value = rankName;
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    #endregion

}



